﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MySql.Data.MySqlClient;
using AVCUMPlatform.HelperLibrary;
using Dm;
using Kdbndp;
using Newtonsoft.Json;

namespace AVCUMPlatform.DAL
{
    /// <summary>
    /// Dapper 框架工具类
    /// </summary>
    public class DapperHelper : IDisposable
    {
        public static string dbConnection = null;
        public static string DbType = null;
        public static int pool = 10;
        private static Dictionary<string, DbConnectionInfo> conns = new Dictionary<string, DbConnectionInfo>();
        private static bool PoolThreadFlag = false;
        private static Thread PoolThread;

        private DbConnection conn = null;
        private DbTransaction transaction = null;

        public DapperHelper()
        {
            try
            {
                switch (DbType)
                {
                    case "dm":
                        conn = new DmConnection(dbConnection);
                        break;
                    case "rdjc":
                        AppContext.SetSwitch("Kdbndp.EnableLegacyCaseInsensitiveDbParameters", true);
                        conn = new KdbndpConnection(dbConnection);
                        break;
                    default:
                        conn = new MySqlConnection(dbConnection);
                        break;
                }
                conn.ConnectionString = dbConnection;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
            }
            catch (Exception ex)
            {
                Log.WriteException(ex);
                throw new Exception("数据库连接失败，请联系技术支持");
            }
        }

        #region Public Static

        public static T GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            try
            {
                DbConnectionInfo connInfo = GetConnInfo();
                if (DbType == "dm")
                {
                    sql = sql.Replace("`", "\"").Replace("@", ":").Replace("SELECT LAST_INSERT_ID();", "Select @@IDENTITY;");
                }
                else if (DbType == "rdjc")
                {
                    sql = sql.Replace("`", "\"").Replace(";SELECT LAST_INSERT_ID();", " RETURNING id;");
                }
                T t = connInfo.conn.ExecuteScalar<T>(sql, param, null, commandTimeout, commandType);
                lock (conns)
                    conns[connInfo.id].isusing = false;
                return t;
            }
            catch (Exception)
            {
                Log.WriteException(new Exception($"DapperHelper 45:{sql}"));
                if (param != null)
                {
                    Log.WriteException(new Exception($"DapperHelper 45:param:{JsonConvert.SerializeObject(param)}"));
                }
                throw;
            }
        }

        public static T GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            try
            {
                DbConnectionInfo connInfo = GetConnInfo();
                if (DbType == "dm")
                {
                    sql = sql.Replace("`", "\"").Replace("@", ":");
                }
                else if (DbType == "rdjc")
                {
                    sql = sql.Replace("`", "\"");
                }
                T t = connInfo.conn.QueryFirstOrDefault<T>(sql, param, null, commandTimeout, commandType);
                lock (conns)
                    conns[connInfo.id].isusing = false;
                return t;
            }
            catch (Exception)
            {
                Log.WriteException(new Exception($"DapperHelper 62:{sql}"));
                if (param != null)
                {
                    Log.WriteException(new Exception($"DapperHelper 62:param:{JsonConvert.SerializeObject(param)}"));
                }
                throw;
            }
        }

        public static List<T> GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            try
            {
                DbConnectionInfo connInfo = GetConnInfo();
                if (DbType == "dm")
                {
                    sql = sql.Replace("`", "\"").Replace("@", ":");
                }
                else if (DbType == "rdjc")
                {
                    sql = sql.Replace("`", "\"");
                }
                List<T> list = connInfo.conn.Query<T>(sql, param, null, buffered, commandTimeout, commandType).ToList();
                lock (conns)
                    conns[connInfo.id].isusing = false;
                return list;
            }
            catch (Exception)
            {
                Log.WriteException(new Exception($"DapperHelper 79:{sql}"));
                if (param != null)
                {
                    Log.WriteException(new Exception($"DapperHelper 79:param:{JsonConvert.SerializeObject(param)}"));
                }
                throw;
            }
        }

        public static bool ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            try
            {
                DbConnectionInfo connInfo = GetConnInfo();
                if (DbType == "dm")
                {
                    sql = sql.Replace("`", "\"").Replace("@", ":").Replace("SELECT LAST_INSERT_ID();", "Select @@IDENTITY;");
                }
                else if (DbType == "rdjc")
                {
                    sql = sql.Replace("`", "\"").Replace(";SELECT LAST_INSERT_ID();", " RETURNING id;");
                }
                int result = connInfo.conn.Execute(sql, param, null, commandTimeout, commandType);
                lock (conns)
                    conns[connInfo.id].isusing = false;
                return (result > 0);
            }
            catch (Exception)
            {
                Log.WriteException(new Exception($"DapperHelper 96:{sql}"));
                if (param != null)
                {
                    Log.WriteException(new Exception($"DapperHelper 96:param:{JsonConvert.SerializeObject(param)}"));
                }
                throw;
            }
        }

        #endregion

        #region Public

        public void UseDbTransaction()
        {
            try
            {
                transaction = conn.BeginTransaction();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void Commit()
        {
            try
            {
                if (transaction.Connection != null && transaction.Connection.State != ConnectionState.Closed)
                {
                    transaction.Commit();
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (transaction.Connection != null && transaction.Connection.State != ConnectionState.Closed)
                {
                    transaction.Connection.Close();
                    transaction.Dispose();
                    transaction = null;
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                    conn = null;
                }
            }
        }

        public void Rollback()
        {
            try
            {
                if (transaction.Connection != null && transaction.Connection.State != ConnectionState.Closed)
                {
                    transaction.Rollback();
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (transaction.Connection != null && transaction.Connection.State != ConnectionState.Closed)
                {
                    transaction.Connection.Close();
                    transaction.Dispose();
                    transaction = null;
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                    conn = null;
                }
            }
        }

        public T _GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            try
            {
                return conn.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType);
            }
            catch (Exception)
            {
                Log.WriteException(new Exception($"DapperHelper 185:{sql}"));
                throw;
            }
        }

        public T _GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            try
            {
                return conn.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);
            }
            catch (Exception)
            {
                Log.WriteException(new Exception($"DapperHelper 198:{sql}"));
                throw;
            }
        }

        public List<T> _GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            try
            {
                return conn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).ToList();
            }
            catch (Exception)
            {
                Log.WriteException(new Exception($"DapperHelper 211:{sql}"));
                throw;
            }
        }

        public bool _ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            try
            {
                int result = conn.Execute(sql, param, transaction, commandTimeout, commandType);
                return (result > 0);
            }
            catch (Exception)
            {
                Log.WriteException(new Exception($"DapperHelper 225:{sql}"));
                throw;
            }
        }
        #endregion

        #region Private

        /// <summary>
        /// 获取一个空闲的数据库链接
        /// </summary>
        /// <returns></returns>
        private static DbConnectionInfo GetConnInfo()
        {
            try
            {
                if (PoolThreadFlag == false)
                {
                    PoolThread = new Thread(_PoolThreadFlag);
                    PoolThread.IsBackground = true;
                    PoolThread.Start();
                    PoolThreadFlag = true;
                }

                lock (conns)
                {
                    DbConnectionInfo connInfo = new DbConnectionInfo();
                    while (true)
                    {
                        try
                        {
                            DbConnectionInfo tempInfo = null;
                            foreach (DbConnectionInfo item in conns.Values)
                            {
                                if (item.isusing == false)
                                {
                                    item.isusing = true;
                                    if (item.conn.State == ConnectionState.Closed)
                                        item.conn.Open();
                                    tempInfo = item;
                                    break;
                                }
                            }
                            if (tempInfo != null)
                            {
                                connInfo = tempInfo;
                                break;
                            }
                            else
                            {
                                if (conns.Count < pool)
                                {
                                    DbConnection conn = null;
                                    switch (DbType)
                                    {
                                        case "dm":
                                            conn = new DmConnection(dbConnection);
                                            break;
                                        case "rdjc":
                                            AppContext.SetSwitch("Kdbndp.EnableLegacyCaseInsensitiveDbParameters", true);
                                            conn = new KdbndpConnection(dbConnection);
                                            break;
                                        default:
                                            conn = new MySqlConnection(dbConnection);
                                            break;
                                    }
                                    conn.ConnectionString = dbConnection;
                                    if (conn.State == ConnectionState.Closed)
                                        conn.Open();
                                    connInfo.id = DateTime.Now.ToString("yyyyMMddHHmmssfff");
                                    connInfo.conn = conn;
                                    connInfo.isusing = true;
                                    conns.Add(connInfo.id, connInfo);
                                    break;
                                }
                            }
                            Thread.CurrentThread.Join(50);
                        }
                        catch (Exception ex)
                        {
                            Log.WriteException(ex);
                            throw new Exception("数据库连接失败，请联系技术支持");
                        }

                    }

                    connInfo.lastUseTime = DateTime.Now;
                    return connInfo;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        private static void _PoolThreadFlag(object obj)
        {
            while (true)
            {
                lock (conns)
                {
                    for (int i = 0; i < conns.Count; i++)
                    {
                        var item = conns.ElementAt(i);
                        TimeSpan ts = DateTime.Now - item.Value.lastUseTime;
                        if (ts.TotalMinutes > 10)
                        {
                            item.Value.conn.Close();
                            item.Value.conn.Dispose();
                            item.Value.conn = null;
                            conns[item.Key] = null;
                            conns.Remove(item.Key);
                            i -= 1;
                        }
                    }
                }
                Thread.CurrentThread.Join(60000);
            }
        }

        #endregion

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        ~DapperHelper()
        {
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
        }
    }

    public class DbConnectionInfo
    {
        /// <summary>
        /// id
        /// </summary>
        public string id { get; set; }

        /// <summary>
        /// 是否使用中
        /// </summary>
        public bool isusing { get; set; }

        /// <summary>
        /// 最后使用时间
        /// </summary>
        public DateTime lastUseTime { get; set; }

        /// <summary>
        /// 连接对象
        /// </summary>
        public DbConnection conn { get; set; }
    }
}
